import datetime
from datetime import datetime,timedelta
import sqlite3
import os
from tkinter import messagebox
import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from werkzeug.security import generate_password_hash, check_password_hash
class Database:
    _instance = None  # 类变量保存单例实例
    _initialized = False  # 跟踪初始化状态

    def __new__(cls, db_path='./library.db'):
        # 单例模式实现
        if not cls._instance:
            # 创建新实例时添加线程锁(可选)
            cls._instance = super(Database, cls).__new__(cls)

            # 初始化数据库连接
            os.makedirs(os.path.dirname(db_path), exist_ok=True)
            cls._instance.conn = sqlite3.connect(db_path)

            # 首次初始化标记
            cls._initialized = False
        return cls._instance
    def __init__(self, db_path='./library.db'):
        if not self._initialized:
            self.create_tables()
            self.insert_sample_data()
            Database._initialized = True
        # Ensure database directory exists
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        self.conn = sqlite3.connect(db_path)
        self.create_tables()
        self.insert_sample_data()
    def create_tables(self):
        """Create database tables"""
        try:
            # Users table
            self.conn.execute('''CREATE TABLE IF NOT EXISTS users (
                   id INTEGER PRIMARY KEY ,
                   username TEXT UNIQUE NOT NULL CHECK(length(username) BETWEEN 3 AND 20),
                   password_hash TEXT NOT NULL,
                   role TEXT CHECK(role IN ('regular', 'librarian', 'sysadmin')) NOT NULL DEFAULT 'regular',
                   email TEXT CHECK(email LIKE '%@%.%'),
                   phone TEXT CHECK(phone IS NULL OR (length(phone) = 11 AND phone GLOB '[0-9]*')),
                   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
               )''')

            # Books table
            self.conn.execute('''CREATE TABLE IF NOT EXISTS books (
                   id INTEGER PRIMARY KEY ,
                   title TEXT NOT NULL,
                   isbn TEXT UNIQUE NOT NULL,
                   author TEXT,
                   category TEXT,
                   status TEXT DEFAULT 'available',
                   added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
               )''')

            # Borrow records table
            self.conn.execute('''CREATE TABLE IF NOT EXISTS borrow_records (
                   id INTEGER PRIMARY KEY,
                   user_id INTEGER NOT NULL,
                   book_id INTEGER NOT NULL,
                   borrow_date TEXT NOT NULL,
                   return_date TEXT,
                   renew_count INTEGER DEFAULT 0, 
                   due_date TEXT,  
                   FOREIGN KEY(user_id) REFERENCES users(id),
                   FOREIGN KEY(book_id) REFERENCES books(id)
               )''')
            self.conn.execute('''CREATE TABLE IF NOT EXISTS logs (
                       id INTEGER PRIMARY KEY AUTOINCREMENT,
                       user_id INTEGER,
                       action TEXT NOT NULL,
                       details TEXT,
                       timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                       FOREIGN KEY(user_id) REFERENCES users(id)
                   )''')
            self.conn.execute('''CREATE TABLE IF NOT EXISTS reservations (
                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                          user_id INTEGER NOT NULL,
                          book_id INTEGER NOT NULL,
                          reserve_date TEXT NOT NULL,      -- 预约日期
                          expire_date TEXT NOT NULL,       -- 预约过期日期(默认预约后3天内有效)
                          status TEXT NOT NULL CHECK(status IN ('pending', 'completed', 'canceled', 'expired')) DEFAULT 'pending',
                          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                          FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
                          FOREIGN KEY(book_id) REFERENCES books(id) ON DELETE CASCADE,
                          UNIQUE(user_id, book_id, reserve_date)  -- 防止同一用户对同一书籍重复预约
                      )''')

            # 创建索引提高查询效率
            self.conn.execute('''CREATE INDEX IF NOT EXISTS idx_reservations_book ON reservations(book_id)''')
            self.conn.execute('''CREATE INDEX IF NOT EXISTS idx_reservations_user ON reservations(user_id)''')
            self.conn.execute('''CREATE INDEX IF NOT EXISTS idx_reservations_status ON reservations(status)''')
            self.conn.commit()
        except sqlite3.Error as e:
            print(f"Failed to create tables: {e}")

    def insert_sample_data(self):

        try:
            # Sample users
            sample_users = [
                ('admin', generate_password_hash('Admin@123'), 'sysadmin', 'admin@library.com', '13800138000'),
                (
                'librarian1', generate_password_hash('Librarian1@123'), 'librarian', 'lib1@library.com', '13800138001'),
                ('user1', generate_password_hash('User1@123'), 'regular', 'user1@example.com', '13800138002'),
            ]
            self.conn.executemany('''INSERT OR IGNORE INTO users 
                                  (username, password_hash, role, email, phone) 
                                  VALUES (?, ?, ?, ?, ?)''', sample_users)

            # Sample books
            sample_books = [
                ('西游记', '9787115428028', '吴承恩', '文学'),
                ('红楼梦', '9787532172403', '曹雪芹', '文学'),
                ('三国演义', '9787020123406', '罗贯中', '文学'),
                ('水浒传', '9787020118112', '施耐庵', '文学'),
            ]
            self.conn.executemany('''INSERT OR IGNORE INTO books 
                                  (title, isbn, author, category) 
                                  VALUES (?, ?, ?, ?)''', sample_books)
            self.conn.commit()
        except sqlite3.Error as e:
            print(f"Failed to initialize sample data: {e}")
            # 打印具体的错误信息
            import traceback
            traceback.print_exc()

    # User operations
    def get_user(self, username):
        try:
            cursor = self.conn.execute('SELECT * FROM users WHERE username = ?', (username,))
            return cursor.fetchone()
        except sqlite3.Error as e:
            print(f"Failed to get user: {e}")
            return None

    def get_all_users(self):
        try:
            cursor = self.conn.execute('SELECT id, username, role, email, phone, created_at FROM users')
            return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"Failed to get all users: {e}")
            return []

    # 数据库方法修改为：
    def update_user(self, original_username, **kwargs):
        """Update user information by original username"""
        if not kwargs:
            return False

        if 'password' in kwargs:
            kwargs['password_hash'] = generate_password_hash(kwargs.pop('password'))

        set_clause = ', '.join(f"{k} = ?" for k in kwargs)
        values = list(kwargs.values())
        values.append(original_username)

        try:
            self.conn.execute(f"UPDATE users SET {set_clause} WHERE username = ?", values)
            self.conn.commit()
            updated_fields = ', '.join(kwargs.keys())
            self.log_action(None, 'USER_UPDATE',
                            f'Original username: {original_username}, Updated fields: {updated_fields}')
            return True
        except sqlite3.Error as e:
            print(f"Failed to update user: {e}")
            return False
    def delete_user(self, user_id):
        try:
            username = self.conn.execute("SELECT username FROM users WHERE id = ?",
                                         (user_id,)).fetchone()[0]

            self.conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
            self.conn.commit()

            # 添加日志
            self.log_action(None, 'USER_DELETE', f'Deleted user: {username}')
            return True
        except sqlite3.Error as e:
            print(f"Failed to delete user: {e}")
            return False

    # Book operations
    def get_books(self, search_term='', category=None):
        try:
            query = '''SELECT b.id, b.title, b.isbn, b.author, b.category, b.status, 
                      u.username FROM books b
                      LEFT JOIN borrow_records br ON b.id = br.book_id AND br.return_date IS NULL
                      LEFT JOIN users u ON br.user_id = u.id
                      WHERE (b.title LIKE ? OR b.isbn LIKE ? OR b.author LIKE ?)'''
            params = [f'%{search_term}%'] * 3

            if category:
                query += ' AND b.category = ?'
                params.append(category)

            cursor = self.conn.execute(query, params)
            return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"Failed to get books: {e}")
            messagebox.showerror("数据库错误", f"查询书籍失败: {str(e)}")
            return []

    def check_overdue_books(self):
        try:
            # 查找超过应还日期未归还的书籍
            cursor = self.conn.execute('''SELECT u.username, b.title, br.due_date 
                                       FROM borrow_records br
                                       JOIN users u ON br.user_id = u.id
                                       JOIN books b ON br.book_id = b.id
                                       WHERE br.return_date IS NULL 
                                       AND date(br.due_date) < date('now')''')
            return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"检查逾期书籍失败: {e}")
            return []

    def reserve_book(self, user_id, book_id, reserve_date):
        try:
            reserve_date_obj = datetime.strptime(reserve_date, "%Y-%m-%d")
            expire_date_obj = reserve_date_obj + timedelta(days=3)
            expire_date = expire_date_obj.strftime("%Y-%m-%d")

            self.conn.execute(
                "INSERT INTO reservations (user_id, book_id, reserve_date, expire_date) VALUES (?, ?, ?, ?)",
                (user_id, book_id, reserve_date, expire_date))
            self.conn.commit()  # 修正这里
            book_title = self.get_book_title(book_id)
            self.log_action(user_id, 'BOOK_RESERVE',
                            f'Book ID: {book_id}, Title: {book_title}, Reserve date: {reserve_date}')
            return True, "预约成功"
        except Exception as e:
            return False, f"预约失败: {str(e)}"
    def get_categories(self):
        """Get all book categories"""
        try:
            cursor = self.conn.execute("SELECT DISTINCT category FROM books WHERE category IS NOT NULL")
            return [row[0] for row in cursor.fetchall()]
        except sqlite3.Error as e:
            print(f"Failed to get categories: {e}")
            return []

    def add_book(self, book_data):
        """Add new book"""
        try:
            self.conn.execute('''INSERT INTO books 
                (title, isbn, author, category)
                VALUES (?, ?, ?, ?)''',
                              (book_data['title'], book_data['isbn'],
                               book_data['author'], book_data['category']))
            self.conn.commit()
            self.log_action(None, 'BOOK_ADD',
                            f'Title: {book_data["title"]}, ISBN: {book_data["isbn"]}')
            return True
        except sqlite3.IntegrityError as e:
            print(f"Failed to add book: {e}")
            return False

    def get_book_title(self, book_id):
        try:
            cursor = self.conn.execute("SELECT title FROM books WHERE id = ?", (book_id,))
            result = cursor.fetchone()  # 修正这里，使用 cursor 而不是 self.cursor
            if result:
                return result[0]  # 返回 title
            else:
                return None  # 如果没有找到书籍，返回 None
        except sqlite3.Error as e:
            print(f"Failed to get book title: {e}")
            return None

    def borrow_book(self, user_id, book_id):
        """Borrow a book"""
        try:
            # Check if book is available
            status = self.conn.execute("SELECT status FROM books WHERE id = ?", (book_id,)).fetchone()
            if not status or status[0] != 'available':
                return False

            # Update book status
            self.conn.execute("UPDATE books SET status = 'borrowed' WHERE id = ?", (book_id,))

            # Create borrow record with due date (30 days from now)
            self.conn.execute('''INSERT INTO borrow_records 
                              (user_id, book_id, borrow_date, due_date)
                              VALUES (?, ?, datetime('now'), datetime('now', '+30 days'))''',
                              (user_id, book_id))
            self.conn.commit()

            # 记录日志
            book_title = self.get_book_title(book_id)
            self.log_action(user_id, 'BORROW', f'Book ID: {book_id}, Title: {book_title}')
            return True

        except sqlite3.Error as e:
            print(f"Failed to borrow book: {e}")
            return False
    def get_book(self, book_id):
        """Get book information"""
        try:
            cursor = self.conn.execute('''SELECT b.id, b.title, b.isbn, b.author, b.category, b.status, 
                                          u.username 
                                          FROM books b
                                          LEFT JOIN borrow_records br ON b.id = br.book_id AND br.return_date IS NULL
                                          LEFT JOIN users u ON br.user_id = u.id
                                          WHERE b.id = ?''', (book_id,))
            return cursor.fetchone()
        except sqlite3.Error as e:
            print(f"未找到该书籍: {e}")
            return None

    def update_book(self, book_id, **kwargs):
        """Update book information"""
        if not kwargs:
            return False

        set_clause = ', '.join(f"{k} = ?" for k in kwargs)
        values = list(kwargs.values())
        values.append(book_id)

        try:
            book_title = self.get_book_title(book_id)
            self.conn.execute(f"UPDATE books SET {set_clause} WHERE id = ?", values)
            self.conn.commit()
            updated_fields = ', '.join(kwargs.keys())
            self.log_action(None, 'BOOK_UPDATE',
                            f'Book ID: {book_id}, Title: {book_title}, Updated fields: {updated_fields}')
            return True
        except sqlite3.Error as e:
            print(f"Failed to update book: {e}")
            return False

    def delete_book(self, book_id):
        try:
            book_title = self.get_book_title(book_id)
            self.conn.execute("DELETE FROM books WHERE id = ?", (book_id,))
            self.conn.commit()
            self.log_action(None, 'BOOK_DELETE',
                            f'Book ID: {book_id}, Title: {book_title}')
            return True
        except sqlite3.Error as e:
            print(f"Failed to delete book: {e}")
            return False

    # Borrow record operations

    # User registration
    def get_all_books(self):
            try:
                cursor = self.conn.execute("SELECT * FROM books")
                return cursor.fetchall()
            except sqlite3.Error as e:
                print(f"Failed to get all books: {e}")
                return []
    def add_user_by_admin(self, username, password, role, email=None, phone=None):
        """管理员添加用户"""
        try:
            if role not in ['regular', 'librarian']:
                return False, "只能添加普通用户或图书管理员"

            if not (3 <= len(username) <= 20):
                return False, "用户名长度必须在3-20个字符之间"

            if len(password) < 6:
                return False, "密码长度至少6位"

            self.conn.execute(
                """INSERT INTO users (username, password_hash, role, email, phone)
                VALUES (?, ?, ?, ?, ?)""",
                (username, generate_password_hash(password), role, email, phone)
            )
            self.conn.commit()
            self.log_action(None, 'USER_ADD', f'Username: {username}, Role: {role}')
            return True, "用户添加成功"
        except sqlite3.IntegrityError as e:
            self.conn.rollback()
            return False, "用户名已存在"
        except Exception as e:
            self.conn.rollback()
            return False, f"添加用户失败: {str(e)}"

    def force_return_book(self, record_id):
        """强制归还书籍(通过借阅记录ID)"""
        try:
            # First get the book_id from the borrow record
            cursor = self.conn.execute('''SELECT book_id FROM borrow_records 
                                       WHERE id = ? AND return_date IS NULL''',
                                       (record_id,))
            record = cursor.fetchone()

            if not record:
                return False, "未找到有效的借阅记录或书籍已归还"

            book_id = record[0]

            # Update book status
            self.conn.execute("UPDATE books SET status = 'available' WHERE id = ?", (book_id,))

            # Update borrow record
            self.conn.execute('''UPDATE borrow_records 
                              SET return_date = datetime('now') 
                              WHERE id = ?''', (record_id,))

            self.conn.commit()
            book_title = self.get_book_title(book_id)
            self.log_action(None, 'BOOK_FORCE_RETURN',
                            f'Record ID: {record_id}, Book ID: {book_id}, Title: {book_title}')
            return True, "强制归还成功"
        except sqlite3.Error as e:
            self.conn.rollback()
            return False, f"强制归还失败: {str(e)}"

    def renew_book(self, book_id, user_id):
        """续借书籍"""
        try:
            # 获取当前借阅记录
            cursor = self.conn.execute('''SELECT id, renew_count, due_date 
                                       FROM borrow_records 
                                       WHERE book_id = ? AND user_id = ? AND return_date IS NULL''',
                                       (book_id, user_id))
            record = cursor.fetchone()

            if not record:
                return False, "未找到有效的借阅记录"

            record_id, renew_count, due_date = record

            # 检查续借次数(假设最多续借1次)
            if renew_count >= 1:
                return False, "已达到最大续借次数"

            # 更新借阅记录
            new_due_date = (datetime.strptime(due_date, "%Y-%m-%d %H:%M:%S") +
                            timedelta(days=30)).strftime("%Y-%m-%d %H:%M:%S")

            self.conn.execute('''UPDATE borrow_records 
                              SET due_date = ?, renew_count = renew_count + 1 
                              WHERE id = ?''',
                              (new_due_date, record_id))
            self.conn.commit()

            # 记录日志
            book_title = self.get_book_title(book_id)
            self.log_action(user_id, 'RENEW', f'Book ID: {book_id}, Title: {book_title}, New due: {new_due_date}')
            return True, "续借成功，新的应还日期为: " + new_due_date

        except Exception as e:
            self.conn.rollback()
            return False, f"续借失败: {str(e)}"
    def backup_database(self, backup_path):
        try:
            with sqlite3.connect(backup_path) as backup_conn:
                self.conn.backup(backup_conn)
            self.log_action(None, 'SYSTEM_BACKUP', f'Backup path: {backup_path}')
            return True, "备份成功"
        except sqlite3.Error as e:
            return False, f"备份失败: {str(e)}"

    def log_action(self, user_id, action, details=None):
        try:
            self.conn.execute('''INSERT INTO logs (user_id, action, details)
                              VALUES (?, ?, ?)''',
                              (user_id, action, details))
            self.conn.commit()
        except sqlite3.Error as e:
            print(f"日志记录失败: {e}")

    # 在Database类中添加以下方法

    def get_user_borrow_records(self, user_id):
        """获取用户借阅记录"""
        try:
            cursor = self.conn.execute('''SELECT br.id, b.title, br.borrow_date, br.due_date, br.return_date 
                                       FROM borrow_records br
                                       JOIN books b ON br.book_id = b.id
                                       WHERE br.user_id = ?''', (user_id,))
            return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"获取借阅记录失败: {e}")
            return []

    def get_all_borrow_records(self):
        """获取所有借阅记录(管理员用)"""
        try:
            cursor = self.conn.execute('''SELECT br.id, u.username, b.title, br.borrow_date, 
                                       br.due_date, br.return_date 
                                       FROM borrow_records br
                                       JOIN books b ON br.book_id = b.id
                                       JOIN users u ON br.user_id = u.id''')
            return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"获取借阅记录失败: {e}")
            return []

    def get_user_reservations(self, user_id):
        """获取用户预约记录"""
        try:
            cursor = self.conn.execute('''SELECT r.id, b.title, r.reserve_date, r.expire_date, r.status
                                       FROM reservations r
                                       JOIN books b ON r.book_id = b.id
                                       WHERE r.user_id = ?''', (user_id,))
            return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"获取预约记录失败: {e}")
            return []

    def get_all_reservations(self):
        """获取所有预约记录(管理员用)"""
        try:
            cursor = self.conn.execute('''SELECT r.id, u.username, b.title, r.reserve_date, 
                                       r.expire_date, r.status
                                       FROM reservations r
                                       JOIN books b ON r.book_id = b.id
                                       JOIN users u ON r.user_id = u.id''')
            return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"获取预约记录失败: {e}")
            return []

    def update_reservation(self, reservation_id, **kwargs):
        """更新预约信息"""
        if not kwargs:
            return False

        set_clause = ', '.join(f"{k} = ?" for k in kwargs)
        values = list(kwargs.values())
        values.append(reservation_id)

        try:
            self.conn.execute(f"UPDATE reservations SET {set_clause} WHERE id = ?", values)
            self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"更新预约失败: {e}")
            return False

    def delete_reservation(self, reservation_id,user_id):
        """删除预约"""
        try:
            res_info = self.conn.execute('''SELECT b.title, r.reserve_date 
                                                 FROM reservations r
                                                 JOIN books b ON r.book_id = b.id
                                                 WHERE r.id = ?''', (reservation_id,)).fetchone()
            self.conn.execute("DELETE FROM reservations WHERE id = ?", (reservation_id,))
            self.conn.commit()
            if res_info:
                self.log_action(user_id, 'RESERVATION_CANCEL',
                                f'Reservation ID: {reservation_id}, Book: {res_info[0]}, Date: {res_info[1]}')
            return True
        except sqlite3.Error as e:
            print(f"删除预约失败: {e}")
            return False
    def import_books_from_excel(self, file_path):
        """从Excel文件导入书籍"""
        try:
            # 读取Excel文件
            df = pd.read_excel(file_path)

            # 检查必要的列是否存在
            required_columns = ['书名', 'ISBN', '作者', '分类']
            for col in required_columns:
                if col not in df.columns:
                    return False, f"Excel文件中缺少必要列: {col}"

            # 开始事务
            self.conn.execute("BEGIN TRANSACTION")

            success_count = 0
            fail_count = 0

            for _, row in df.iterrows():
                try:
                    book_data = {
                        'title': row['书名'],
                        'isbn': str(row['ISBN']),
                        'author': row['作者'],
                        'category': row['分类'],
                        'status': row.get('状态', 'available')
                    }

                    # 添加书籍
                    if self.add_book(book_data):
                        success_count += 1
                    else:
                        fail_count += 1
                except Exception as e:
                    fail_count += 1
                    print(f"导入书籍失败: {str(e)}")

            # 提交事务
            self.conn.commit()
            self.log_action(None, 'BOOK_IMPORT',
                            f'File: {file_path}, Success: {success_count}, Failed: {fail_count}')
            return True, f"导入完成: 成功 {success_count} 条, 失败 {fail_count} 条"
        except Exception as e:
            self.conn.rollback()
            return False, f"导入失败: {str(e)}"
    def export_borrow_records_to_excel(self, file_path, record_ids=None):
        """导出借阅记录到Excel文件"""
        try:
            if record_ids:
                # 导出指定ID的记录
                placeholders = ','.join(['?'] * len(record_ids))
                query = f"""
                    SELECT br.id, u.username, b.title, br.borrow_date, 
                           br.due_date, br.return_date, br.renew_count
                    FROM borrow_records br
                    JOIN books b ON br.book_id = b.id
                    JOIN users u ON br.user_id = u.id
                    WHERE br.id IN ({placeholders})
                """
                records = self.conn.execute(query, record_ids).fetchall()
            else:
                # 导出所有记录
                records = self.conn.execute('''
                    SELECT br.id, u.username, b.title, br.borrow_date, 
                           br.due_date, br.return_date, br.renew_count
                    FROM borrow_records br
                    JOIN books b ON br.book_id = b.id
                    JOIN users u ON br.user_id = u.id
                ''').fetchall()

            # 创建DataFrame
            df = pd.DataFrame(records,
                              columns=['记录ID', '用户名', '书名', '借阅日期', '应还日期', '归还日期', '续借次数'])

            # 导出到Excel
            df.to_excel(file_path, index=False)
            record_count = len(records)
            self.log_action(None, 'RECORD_EXPORT',
                            f'File: {file_path}, Records exported: {record_count}')
            return True, "导出成功"
        except Exception as e:
            return False, f"导出失败: {str(e)}"

    def export_selected_books_to_excel(self, file_path, book_ids=None):
        """导出指定书籍到Excel文件"""
        try:
            if book_ids:
                # 导出指定ID的书籍
                placeholders = ','.join(['?'] * len(book_ids))
                query = f"SELECT * FROM books WHERE id IN ({placeholders})"
                books = self.conn.execute(query, book_ids).fetchall()
            else:
                # 导出所有书籍
                books = self.get_all_books()

            # 创建DataFrame
            df = pd.DataFrame(books, columns=['ID', '书名', 'ISBN', '作者', '分类', '状态', '添加日期'])

            # 导出到Excel
            df.to_excel(file_path, index=False)
            return True, "导出成功"
        except Exception as e:
            return False, f"导出失败: {str(e)}"

    def delete_user_u(self, user_id):
        try:
            # 先删除关联记录
            self.conn.execute("DELETE FROM borrow_records WHERE user_id = ?", (user_id,))
            self.conn.execute("DELETE FROM reservations WHERE user_id = ?", (user_id,))
            self.conn.execute("DELETE FROM logs WHERE user_id = ?", (user_id,))

            # 再删除用户
            username = self.conn.execute("SELECT username FROM users WHERE id = ?",
                                         (user_id,)).fetchone()[0]
            self.conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
            self.conn.commit()

            self.log_action(None, 'USER_DELETE', f'Deleted user: {username}')
            return True
        except sqlite3.Error as e:
            print(f"Failed to delete user: {e}")
            self.conn.rollback()
            return False

    def get_category_borrow_stats(self):
        """获取分类借阅统计"""
        try:
            cursor = self.conn.execute('''
                SELECT b.category, COUNT(br.id) AS borrow_count
                FROM borrow_records br
                JOIN books b ON br.book_id = b.id
                GROUP BY b.category
                ORDER BY borrow_count DESC
            ''')
            return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"获取分类借阅统计失败: {e}")
            return []

    def get_popular_books(self, limit=10):
        """获取热门书籍"""
        try:
            cursor = self.conn.execute('''
                SELECT b.id, b.title, COUNT(br.id) AS borrow_count
                FROM books b
                LEFT JOIN borrow_records br ON b.id = br.book_id
                GROUP BY b.id
                ORDER BY borrow_count DESC
                LIMIT ?
            ''', (limit,))
            return cursor.fetchall()
        except sqlite3.Error as e:
            print(f"获取热门书籍失败: {e}")
            return []

    def get_total_borrow_count(self):
        """获取总借阅次数"""
        try:
            cursor = self.conn.execute("SELECT COUNT(*) FROM borrow_records")
            return cursor.fetchone()[0]
        except sqlite3.Error as e:
            print(f"获取总借阅次数失败: {e}")
            return 0